package Model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.sun.rowset.CachedRowSetImpl;

import Database.*;
import Helper.*;

public class UserM extends Model {
    ArrayList<User> cache = null;

    public User login(String name, String password){
        UserM userM = new UserM();
        ArrayList<User> temp = userM.find(name);

        User result = null;

        if( temp.size() == 1 ){
            User u = temp.get(0);

            if( u.name.equals(name) && u.getPassword().equals(Helper.md5(password)) ){
                u.setStatus(name, Helper.md5(password));

                result = u;
            }else{
                System.err.println("用户名或密码错误！");
            }
        }else{
            System.err.println("用户名不存在！");
        }

        return result;
    }

    public boolean signup(String name, String password){
        User u = new User(name,Helper.md5(password));

        UserM userM = new UserM();

        return userM.add(u);
    }

    public ArrayList<Product> getProductList(int id){
        // list buy record
        ArrayList<Product> products = new ArrayList<>();

        UserDB db = new UserDB();
        CachedRowSetImpl res = db.selectProductList(id);

        try{
            while( res.next() ){

                Product p = new Product(res.getString(2),res.getFloat(3),res.getInt(4));
                p.id = res.getInt(1);;

                products.add(p);
            }
        }catch (SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }

        return products;
    }

    public boolean buy(int uid, int pid, int amount){
        UserDB db = new UserDB();

        return db.buy(uid, pid, amount);
    }

    private boolean add(User u){
        UserDB db = new UserDB();

        return db.insert(u);
    }

    private ArrayList<User> all(){
        super.all("user");
        // result is cached

        return refine();
    }

    private ArrayList<User> find(int id){
        super.find("user", id);
        // result is cached

        return refine();
    }

    private ArrayList<User> find(String name){
        UserDB db = new UserDB();

        CachedRowSetImpl res = db.match(name);

        return createuserArr(res);
    }

    protected void init(String tableName){
        // override
        UserDB db = new UserDB();

        CachedRowSetImpl res = db.get(tableName);

        cache = createuserArr(res);
    }

    private ArrayList<User> createuserArr(CachedRowSetImpl res){
        // override

        ArrayList<User> result = new ArrayList<>();

        try {
            while (res.next()) {
                User u = new User(res.getString(2),res.getString(3));
                u.id = res.getInt(1);

                result.add(u);
            }
        } catch (SQLException exception) {
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }

        return result;
    }

    private ArrayList<User> refine(){
        // every time refine is called, init of User must be called before it!
        // so cache is the exact data for refine !

        ArrayList<User> target = new ArrayList<>();

        for( User item : cache ){
            User temp = new User(item.name,item.getPassword());
            temp.id = item.id;

            target.add(temp);
        }

        return target;
    }
}

class UserDB extends DB{
    boolean insert(User u){
        int result = -1;

        try{
            connect();

            String sql = "INSERT INTO user(name,password) VALUES(?,?)";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setString(1,u.name);
            statement.setString(2,u.getPassword());

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("用户已存在！或其他数据库错误");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    CachedRowSetImpl match(String name){
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            PreparedStatement statement = dbConnection.prepareStatement("SELECT * FROM user WHERE name=?");
            statement.setString(1, name);

            ResultSet res = statement.executeQuery();

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    CachedRowSetImpl selectProductList(int uid){
        CachedRowSetImpl cachedRowSet = null;

        try{
            connect();

            String sql = "SELECT p.id,p.name,p.price,b.amount FROM buy b,product p WHERE b.uid=? AND p.id=b" +
                    ".pid";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setInt(1, uid);

            ResultSet res = statement.executeQuery();

            cachedRowSet = new CachedRowSetImpl();
            cachedRowSet.populate(res);
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return cachedRowSet;
    }

    private boolean existBuyRecord(int uid, int pid){
        boolean status = false;

        try{
            connect();

            String sql = "SELECT * FROM buy WHERE uid=? AND pid=?";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setInt(1,uid);
            statement.setInt(2,pid);

            ResultSet res = statement.executeQuery();

            if( res.next() ){
                status = true;
            }
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        return status;
    }

    private boolean insertBuyRecord(int uid, int pid, int amount){
        int result = -1;

        try{
            connect();

            String sql = "INSERT INTO buy(uid,pid,amount) VALUES(?,?,?)";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setInt(1,uid);
            statement.setInt(2,pid);
            statement.setInt(3,amount);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    private boolean updateBuyRecord(int uid, int pid, int amount){
        int result = -1;

        try{
            connect();

            String sql = "UPDATE buy SET amount=amount+? WHERE uid=? AND pid=?";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setInt(1,amount);
            statement.setInt(2,uid);
            statement.setInt(3,pid);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    private boolean reduceProductAmount(int pid, int amount){
        int result = -1;

        try{
            connect();

            String sql = "UPDATE product SET amount=amount-? WHERE id=?";
            PreparedStatement statement = dbConnection.prepareStatement(sql);
            statement.setInt(1,amount);
            statement.setInt(2,pid);

            result = statement.executeUpdate();
        }catch(SQLException exception){
            System.err.println("数据库出错！");
            if( Helper.isDev ){
                exception.printStackTrace();
            }
        }finally {
            close();
        }

        if( result >= 0 ){
            return true;
        }else{
            return false;
        }
    }

    boolean buy(int uid, int pid, int amount){
        ProductM productM = new ProductM();
        ArrayList<Product> p = productM.find(pid);
        int oldAmount = p.get(0).amount;

        if( oldAmount < amount ){
            System.err.println(p.get(0).name+"储量不足！");
            return false;
        }else{
            if( existBuyRecord(uid, pid) ){
                return updateBuyRecord(uid, pid, amount) && reduceProductAmount(pid, amount);
            }else{
                return insertBuyRecord(uid, pid, amount) && reduceProductAmount(pid, amount);
            }
        }
    }
}
